package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.situ.personal.entity.User;
import com.situ.personal.util.JDBCUtil;
import com.situ.personal.util.ModelConvertUtil;

public class UserDao {

	public int getTotalCount() {	
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("总页数SQL/UserDao24Line"+preparedStatement); //测试   打印SQL语句
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				count = resultSet.getInt(1); // 计算总个数，，结果集只有一行，就去取这行的值
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return count;
	}

	public List<User> fandPageInfo(int offset, int pageSize) {
		List<User> list = new ArrayList<User>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,password,email,department_id from user limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("每页现实的内容SQL/UserDao47Line"+preparedStatement);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				int departmentId =  resultSet.getInt("department_id");
				User user  = new User(id, name, age, password, email, departmentId);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return list;
	}
	

	public List<Map<String, Object>> fandPageInfo2(int offset, int pageSize) {
		List<Map<String, Object>> list = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id,u.name,u.age,u.email,u.password,d.name as deptName FROM `user` AS u LEFT JOIN department AS d ON u.department_id=d.id limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("每页现实的内容SQL/UserDao47Line"+preparedStatement);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			resultSet = preparedStatement.executeQuery();
			list = ModelConvertUtil.converToList(resultSet);
//			while (resultSet.next()) {
//				int id = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				int age = resultSet.getInt("age");
//				String password =  resultSet.getString("password");
//				String email = resultSet.getString("email");
//				int departmentId =  resultSet.getInt("department_id");
//				User user  = new User(id, name, age, password, email, departmentId);
//				list.add(user);
//			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return list;
	}

	public boolean insert(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean isSuccess = false;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO user(`name`,age,email,password,department_id) VALUES(?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("添加SQL/UserDao78Line"+preparedStatement);
			preparedStatement.setString(1,user.getName() );
			preparedStatement.setInt(2, user.getAge());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setString(4, user.getPassword());
			preparedStatement.setInt(5, user.getDepartmentId());
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isSuccess = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return isSuccess;
	}

	public boolean deleteById(int id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean isSuccess = false;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("删除功能SQL/UserDao105Line："+preparedStatement);
			preparedStatement.setInt(1,id);
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isSuccess = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return isSuccess;
	}

	public boolean updateById(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean isSuccess = false;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update user set name=?,age=?,password=?,email=?,department_id=? where id=?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("修改功能SQL/UserDao129Line："+preparedStatement);
			preparedStatement.setInt(2,user.getAge());
			preparedStatement.setInt(5,user.getDepartmentId());
			preparedStatement.setInt(6,user.getId());
			preparedStatement.setString(1,user.getName());
			preparedStatement.setString(3,user.getPassword());
			preparedStatement.setString(4,user.getEmail());
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isSuccess = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return isSuccess;
	}

	public User selectById(int selectId) {
		User user = new User();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select * from user where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, selectId);
			System.out.println("selectById_SQL/UserDao158Line"+preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				int departmentId =  resultSet.getInt("department_id");
				user  = new User(id, name, age, password, email, departmentId);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return user;
	}

	public User isLogin(String loginName, String loginPassword) {
		User user = null;
		// User user = new User();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,`name`,`password` FROM USER WHERE `name`=? AND `password`=?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("isLogin_SQL/UserDao187Line："+preparedStatement);
			preparedStatement.setString(1, loginName);
			preparedStatement.setString(2, loginPassword);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password =  resultSet.getString("password");
				
				user  = new User(id, name, null, password, null,null);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return user;
	}

		public List<User> findAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<User> userlist = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,password,email,department_id from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				int departmentId =  resultSet.getInt("department_id");
				User user = new User(id, name, age, password, email, departmentId);
				userlist.add(user);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return userlist;
	}



	
	

}
